/**
 * @FileName: ActivityBusinessSalesService.java
 * @Author
 * @Description:
 * @Date 2017年5月18日 下午2:04:12
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.service.ds.DsLog;
import cn.hpclub.server.util.InterfaceUtil;

public class ActivityBusinessReportService{
    private static final String                 SQL_FILTER_CHANNEL_OPEN         = "oc.WebsiteId=4605 OR oc.WebsiteId=4617 OR oc.WebsiteId=4620 OR oc.WebsiteId=4618 OR oc.WebsiteId=4626 OR oc.WebsiteId=4701 OR oc.WebsiteId=4612";
    private static final String                 SQL_FILTER_CHANNEL_VILLAGE      = "oc.WebsiteId = 4603";
    // 渠道类型：统计月报表使用
    public static final int                     CHANNEL_TYPE_VILLAGE            = 0;                                                                                                                                                // 村渠店员
    public static final int                     CHANNEL_TYPE_OPEN               = 1;                                                                                                                                                // 开渠店员
    public static final int                     CHANNEL_TYPE_OTHERS             = 2;                                                                                                                                                // 其他渠道店员
    public static final int                     CHANNEL_TYPE_OPEN_FIRM          = 3;                                                                                                                                                // 开渠厂促店员
    public static final int                     CHANNEL_TYPE_ALL                = 4;                                                                                                                                                // 所有店员
    // 统计类型
    public static final int                     COUNT_TYPE_DAILY_LIVENESS       = 0;                                                                                                                                                // 当日活跃用户数
    public static final int                     COUNT_TYPE_DAILY_20_YUAN        = 1;                                                                                                                                                // 当日成功领取20元用户数
    public static final int                     COUNT_TYPE_DAILY_PRODUCT        = 2;                                                                                                                                                // 当日4G+全网手机发展量
    public static final int                     COUNT_TYPE_DAILY_PRODUCT_30YUAN = 3;                                                                                                                                                // 当日成功领取终端销售奖励用户数

    public static ActivityBusinessReportService service                         = new ActivityBusinessReportService();

    // 获取村渠活跃度
    public long getLiveness4ChannelVillage(String startDate, String endDate, int orgId){
        return getLiveness(startDate, endDate, SQL_FILTER_CHANNEL_VILLAGE, orgId);
    }

    // 获取开渠活跃度
    public long getLiveness4ChannelOpen(String startDate, String endDate, int orgId){
        return getLiveness(startDate, endDate, SQL_FILTER_CHANNEL_OPEN, orgId);
    }

    // 获取开渠厂促活跃度
    public long getLiveness4ChannelOpenFirm(String startDate, String endDate, int orgId){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT userId) AS cnt FROM sales_promotion WHERE time>=? AND time<=? AND userid in (SELECT id FROM member_org WHERE user_name IN (SELECT phone_no FROM member_kaiquchangcu)) AND org_id IN "
                + chlidOrgIdSql;
        record = Db.findFirst(sql, getStartTime(startDate), getEndTime(endDate));
        return record.getLong("cnt");
    }

    // 获取整体活跃度
    public long getLivenessChannelAll(String startDate, String endDate, int orgId){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT userId) AS cnt FROM sales_promotion WHERE time>=? AND time<=? AND org_id IN "
                + chlidOrgIdSql;
        record = Db.findFirst(sql, getStartTime(startDate), getEndTime(endDate));

        return record.getLong("cnt");
    }

    /**
     * @Title: getCountFromDailyBuild
     * @Description:获取指定时间内统计数据
     * @param startDate开始时间
     * @param endDate结束时间
     * @param countType统计类型0日活跃度，1当日成功领取20元用户数,2当日4G+全网手机发展量,3当日成功领取终端销售奖励用户数
     * @param orgClass渠道类型0村渠店员;1开渠店员;2其他渠道店员;3开渠厂促店员;4所有店员
     * @param orgId机构ID支持省级，地市，区县机构
     * @return long
     */
    public long getCountFromDailyCount(int countType, int orgClass, String month, int orgId){
        String sql = "SELECT count FROM activity_salse_promotion_daily_count WHERE type=? AND org_class=? AND month=? AND org_id=?";
        Record record = Db.findFirst(sql, countType, orgClass, month, orgId);
        if(record == null){
            DsLog.log("无数据：countType:" + countType + ",orgClass=" + orgClass + ",month=" + month + ",orgId=" + orgId);
            return 0;
        }
        return record.getLong("count");
    }

    private String getEndTime(String date){
        return date + "23:59:59";
    }

    private String getStartTime(String date){
        return date + " 00:00:00";
    }

    /**
     * @Title: getSalesPromotionLiveness
     * @Description:计算指定范围内的活跃度
     * @param orgId
     * @param startDate开始日期
     * @param endDate结束日期
     * @param orgClassTypeFilter渠道类型过滤条件
     * @return long活跃度
     */
    private long getLiveness(String startDate, String endDate, String orgClassTypeFilter, int orgId){
        String startTime = getStartTime(startDate);
        String endTime = getEndTime(endDate);
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT userId) AS cnt FROM sales_promotion WHERE time>=? AND time<=? AND userid IN ( SELECT mo.id FROM member_org mo WHERE mo.org_id IN ( SELECT org.id FROM organization org, organization_class oc WHERE mo.org_id = org.id AND org.WebsiteId = oc.id AND (%s) AND org.id IN %s) AND mo.user_name NOT IN ( SELECT phone_no FROM member_kaiquchangcu ))";
        record = Db.findFirst(String.format(sql, orgClassTypeFilter, chlidOrgIdSql), startTime, endTime);

        return record.getLong("cnt");
    }

    // 获取村渠活跃度
    public long get4gSales4ChannelVillage(String startDate, String endDate, int orgId){
        return get4gSales(startDate, endDate, SQL_FILTER_CHANNEL_VILLAGE, orgId);
    }

    // 获取开渠活跃度
    public long get4gSales4ChannelOpen(String startDate, String endDate, int orgId){
        return get4gSales(startDate, endDate, SQL_FILTER_CHANNEL_OPEN, orgId);
    }

    // 获取4G终端销量
    private long get4gSales(String startDate, String endDate, String orgClassTypeFilter, int orgId){
        String startTime = getStartTime(startDate);
        String endTime = getEndTime(endDate);
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(order_info.id) AS cnt FROM activity_sales_promotion asp, order_info"
                + " WHERE order_info.boss_order_id=asp.order_id AND "
                + " order_info.create_time>=? AND order_info.create_time<=? AND business_type=8"
                + " AND login_phone_no IN ( SELECT mo.user_name FROM member_org mo WHERE"
                + " mo.org_id IN ( SELECT org.id FROM organization org, organization_class oc"
                + " WHERE mo.org_id = org.id AND org.WebsiteId = oc.id AND (%s) AND org.id IN %s) AND mo.user_name"
                + " NOT IN ( SELECT phone_no FROM member_kaiquchangcu ))";
        record = Db.findFirst(String.format(sql, orgClassTypeFilter, chlidOrgIdSql), startTime, endTime);
        return record.getLong("cnt");
    }

    // 获取开渠厂促4G终端销量
    public long get4gSales4ChannelOpenFirm(String startDate, String endDate, int orgId){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(order_info.id) AS cnt FROM activity_sales_promotion asp, order_info"
                + " WHERE order_info.boss_order_id=asp.order_id AND "
                + " order_info.create_time>=? AND order_info.create_time<=? AND business_type=8 AND login_phone_no IN (SELECT user_name FROM member_org WHERE user_name IN (SELECT phone_no FROM member_kaiquchangcu) AND org_id IN "
                + chlidOrgIdSql + ")";
        record = Db.findFirst(sql, getStartTime(startDate), getEndTime(endDate));
        return record.getLong("cnt");
    }

    // 获取整体活4G终端销量
    public long get4gSalesLivenessChannelAll(String startDate, String endDate, int orgId){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(order_info.id) AS cnt FROM activity_sales_promotion asp, order_info"
                + " WHERE order_info.boss_order_id=asp.order_id AND "
                + " order_info.create_time>=? AND order_info.create_time<=? AND business_type=8 AND login_phone_no IN ( SELECT mo.user_name FROM member_org mo WHERE"
                + " mo.org_id IN (" + chlidOrgIdSql + "))";
        record = Db.findFirst(sql, getStartTime(startDate), getEndTime(endDate));
        return record.getLong("cnt");
    }

    // 计算送费
    // 获取村渠送费
    public long getReturnedFare4ChannelVillage(String startDate, String endDate, int orgId, int amount){
        return getReturnedFare(startDate, endDate, SQL_FILTER_CHANNEL_VILLAGE, orgId, amount);
    }

    // 获取开渠送费
    public long getReturnedFare4ChannelOpen(String startDate, String endDate, int orgId, int amount){
        return getReturnedFare(startDate, endDate, SQL_FILTER_CHANNEL_OPEN, orgId, amount);
    }

    private long getReturnedFare(String startDate, String endDate, String orgClassTypeFilter, int orgId, int amount){
        String startTime = getStartTime(startDate);
        String endTime = getEndTime(endDate);
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT phone_no) AS cnt FROM activity_sales_promotion_returned_fare"
                + " WHERE  amount=? AND opt_time>=? AND opt_time<=? AND phone_no IN ( SELECT mo.user_name FROM member_org mo WHERE"
                + " mo.org_id IN ( SELECT org.id FROM organization org, organization_class oc"
                + " WHERE mo.org_id = org.id AND org.WebsiteId = oc.id AND (%s) AND org.id IN %s) AND mo.user_name"
                + " NOT IN ( SELECT phone_no FROM member_kaiquchangcu ))";
        record = Db.findFirst(String.format(sql, orgClassTypeFilter, chlidOrgIdSql), amount, startTime, endTime);
        return record.getLong("cnt");
    }

    // 获取开渠厂促送费
    public long getReturnedFare4ChannelOpenFirm(String startDate, String endDate, int orgId, int amount){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT phone_no) AS cnt FROM activity_sales_promotion_returned_fare"
                + " WHERE  amount=? AND opt_time>=? AND opt_time<=? AND phone_no IN (SELECT user_name FROM member_org WHERE user_name IN (SELECT phone_no FROM member_kaiquchangcu) AND org_id IN "
                + chlidOrgIdSql + ")";
        record = Db.findFirst(sql, amount, getStartTime(startDate), getEndTime(endDate));
        return record.getLong("cnt");
    }

    // 获取整体活送费
    public long getReturnedFare4ChannelAll(String startDate, String endDate, int orgId, int amount){
        String chlidOrgIdSql = InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true);
        Record record = null;
        String sql = "SELECT COUNT(DISTINCT phone_no) AS cnt FROM activity_sales_promotion_returned_fare"
                + " WHERE  amount=? AND opt_time>=? AND opt_time<=? AND phone_no IN "
                + " (SELECT user_name FROM member_org WHERE org_id IN " + chlidOrgIdSql + ")";
        record = Db.findFirst(sql, amount, getStartTime(startDate), getEndTime(endDate));
        return record.getLong("cnt");
    }
}
